Synopsis: Rounding Errors

Let’s imagine that your boss asks you to produce a report of the cost of programmer time for the project, based on the total work needed to fix each bug. Each programmer in the Accounts table has a different hourly rate, so you record the number of hours required to fix each bug in the Bugs table, and you multiply it by the hourly_rate of the programmer assigned to do the work.

It would be best to create new columns in the Bugs and Accounts tables to support this query. Both columns should support fractional values because you need to track the costs precisely. You decide to define the new columns as FLOAT because this data type supports fractional values.

Let’s see the effects of adding the FLOAT data type for hours and hourly_rate.

You update the columns with information from the bug work logs and the programmers’ rates, test the report, and call it a day.

The next day, your boss shows up in your office with a copy of the project cost report. “These numbers don’t add up,” they tell you through gritted teeth. “I did the calculation by hand for comparison, and your report is inaccurate — even if only by a few dollars. How do you explain this?” You start to perspire. What could have gone wrong with such a simple calculation?

Well, we’ll look into what went wrong in detail in the next lesson.

Objective: Use fractional numbers instead of integers#

The integer is a useful data type, but it stores only whole numbers like 1, 327, or -19. It can’t represent fractional values like 2.5. We need a different data type if we need numbers with more precision than an integer. For example, sums of money are usually represented by numbers with two decimal places, like $19.95.

So, the objective is to store numeric values that aren’t whole numbers and use them in arithmetic computations. There is an additional objective, although it ought to go without saying: the results of arithmetic computations must be correct.

Legitimate uses of the antipattern#

The FLOAT type is a good data type for when we need real number values with a greater range than what’s supported by INTEGER or NUMERIC data types. Scientific applications are often cited as the best use of a FLOAT. Oracle uses the FLOAT data type to mean an exact scaled numeric, whereas the BINARY_FLOAT data type is an inexact numeric, using the IEEE 754 encoding.